import pymysql
from pymysql.cursors import DictCursor

# MySQL 数据库连接配置
# 请根据您的MySQL服务器配置修改以下参数
DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',
    'port': 3306,
    'charset': 'utf8mb4'
}

# 创建数据库和表的SQL语句
CREATE_DATABASE_SQL = 'CREATE DATABASE IF NOT EXISTS mobile_test_platform DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'

# 表创建语句（根据模型定义）
CREATE_TABLES_SQL = [
    '''
    CREATE TABLE IF NOT EXISTS devices (
        id INT PRIMARY KEY AUTO_INCREMENT,
        device_id VARCHAR(100) NOT NULL UNIQUE,
        device_name VARCHAR(200),
        model VARCHAR(200),
        brand VARCHAR(100),
        os_version VARCHAR(50),
        status VARCHAR(50) DEFAULT 'offline',
        ip_address VARCHAR(50),
        port INT,
        battery_level INT DEFAULT 0,
        is_usb_connected BOOLEAN DEFAULT TRUE,
        current_task_id INT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (current_task_id) REFERENCES tasks(id)
    );
    ''',
    '''
    CREATE TABLE IF NOT EXISTS scripts (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(200) NOT NULL,
        description TEXT,
        content TEXT NOT NULL,
        file_path VARCHAR(255),
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    );
    ''',
    '''
    CREATE TABLE IF NOT EXISTS tasks (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(200) NOT NULL,
        description TEXT,
        script_id INT NOT NULL,
        status VARCHAR(50) DEFAULT 'pending',
        priority INT DEFAULT 1,
        start_time DATETIME,
        end_time DATETIME,
        created_by VARCHAR(100),
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (script_id) REFERENCES scripts(id)
    );
    '''
]

def main():
    try:
        # 连接MySQL服务器（不指定数据库）
        conn = pymysql.connect(**DB_CONFIG, cursorclass=DictCursor)
        cursor = conn.cursor()
        
        print("正在连接MySQL服务器...")
        
        # 创建数据库
        print("正在创建数据库 mobile_test_platform...")
        cursor.execute(CREATE_DATABASE_SQL)
        conn.commit()
        
        # 选择创建的数据库
        cursor.execute("USE mobile_test_platform;")
        
        # 创建表结构
        print("正在创建表结构...")
        for create_table_sql in CREATE_TABLES_SQL:
            cursor.execute(create_table_sql)
        conn.commit()
        
        print("MySQL数据库初始化成功！")
        
    except pymysql.MySQLError as e:
        print(f"MySQL操作失败: {e}")
    finally:
        # 关闭连接
        if 'cursor' in locals():
            cursor.close()
        if 'conn' in locals():
            conn.close()

if __name__ == "__main__":
    main()